package com.runa.persistence.repository;

import com.runa.monitor.comm.entity.CommNetEquipment;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;


@Repository
public interface CommNetEquipmentRepository extends JpaRepository<CommNetEquipment, String> {

    //    @Query("select id,name,serial,service from CommNetEquipment  where id = :#{#commNetEquipment.id}")
//    @Query(value = "select * from comm_net_equipment s where 1=1 and IF(#{commNetEquipment.id} !=null,s.id=#{commNetEquipment.id},1=1)",nativeQuery = true)
    @Query(value = "select neq.id,neq.name,neq.serial,neq.type,neq.model,neq.timeout,ns.name as serviceName from comm_net_equipment as neq left join comm_net_service as ns on neq.service=ns.id  where 1=1  and IF(ISNULL(:id),1=1,neq.id like  concat('%',:id,'%')) " +
            "and IF(ISNULL(:serial),1=1,neq.serial like  %:serial%) and IF(ISNULL(:serviceName),1=1,ns.name like  %:serviceName%)" +
            "and IF(ISNULL(:name),1=1,neq.name like  %:name%) and IF(ISNULL(:model),1=1,neq.model like  %:model%)"
            , countQuery = "select count(neq.id) from comm_net_equipment as neq left join comm_net_service as ns on neq.service=ns.id  where 1=1  and IF(ISNULL(:id),1=1,neq.id like  concat('%',:id,'%')) " +
            "and IF(ISNULL(:serial),1=1,neq.serial like  %:serial%) and IF(ISNULL(:serviceName),1=1,ns.name like  %:serviceName%)" +
            "and IF(ISNULL(:name),1=1,neq.name like  %:name%) and IF(ISNULL(:model),1=1,neq.model like  %:model%)", nativeQuery = true)
    Page<Object> findByCriOfPage(@Param("id") String id, @Param("serial") String serial
            , @Param("serviceName") String serviceName, @Param("name") String name, @Param("model") String model
            , @Param("pageable") Pageable pageable);

    @Query(value = "SELECT id,name,serial,communication_type,region,model,port,sequence,statute_code,relay_route,'表' as type,net_equipment_id,factory_name" +
            " FROM comm_meter " +
            " WHERE 1=1 and IF(ISNULL(:id),1=1,id like  concat('%',:id,'%')) " +
            " and IF(ISNULL(:name),1=1,name like  concat('%',:name,'%')) " +
            " and IF(ISNULL(:serial),1=1,serial like  concat('%',:serial,'%')) " +
            " and IF(ISNULL(:netEquSerial),1=1,net_equipment_id like  concat('%',:netEquSerial,'%'))" +
            " and IF(ISNULL(:model),1=1,model like  concat('%',:model,'%'))" +
            " UNION  ALL " +
            " SELECT id,name,serial,communication_type,region,model,port,sequence,statute_code,relay_route,'阀' as type,net_equipment_id,factory_name " +
            " FROM comm_valve " +
            " WHERE 1=1 and IF(ISNULL(:id),1=1,id like  concat('%',:id,'%')) " +
            " and IF(ISNULL(:name),1=1,name like  concat('%',:name,'%'))  " +
            " and IF(ISNULL(:serial),1=1,serial like  concat('%',:serial,'%')) " +
            " and IF(ISNULL(:netEquSerial),1=1,net_equipment_id like  concat('%',:netEquSerial,'%'))" +
            " and IF(ISNULL(:model),1=1,model like  concat('%',:model,'%'))" +
            " UNION  ALL " +
            " SELECT id,name,serial,communication_type,'' region,model,port,sequence,statute_code,'' relay_route,'面板' as type,net_equipment_id,factory_name " +
            " FROM comm_panel " +
            " WHERE 1=1 and IF(ISNULL(:id),1=1,id like  concat('%',:id,'%')) " +
            " and IF(ISNULL(:name),1=1,name like  concat('%',:name,'%'))  " +
            " and IF(ISNULL(:serial),1=1,serial like  concat('%',:serial,'%')) " +
            " and IF(ISNULL(:netEquSerial),1=1,net_equipment_id like  concat('%',:netEquSerial,'%'))" +
            " and IF(ISNULL(:model),1=1,model like  concat('%',:model,'%'))"
            , countQuery = "select count(ss.id) from " +
            "(SELECT id,name,serial,communication_type,region,model,port,sequence,statute_code,relay_route " +
            " FROM comm_meter " +
            " WHERE 1=1 and IF(ISNULL(:id),1=1,id like  concat('%',:id,'%')) " +
            " and IF(ISNULL(:name),1=1,name like concat('%',:name,'%'))  " +
            " and IF(ISNULL(:serial),1=1,serial like  concat('%',:serial,'%')) " +
            " and IF(ISNULL(:netEquSerial),1=1,net_equipment_id like  concat('%',:netEquSerial,'%'))" +
            " and IF(ISNULL(:model),1=1,model like  concat('%',:model,'%'))" +
            " UNION  ALL " +
            " SELECT id,name,serial,communication_type,region,model,port,sequence,statute_code,relay_route " +
            " FROM comm_valve " +
            " WHERE 1=1 and IF(ISNULL(:id),1=1,id like  concat('%',:id,'%')) " +
            " and IF(ISNULL(:name),1=1,name like  concat('%',:name,'%'))  " +
            " and IF(ISNULL(:serial),1=1,serial like  concat('%',:serial,'%')) " +
            " and IF(ISNULL(:netEquSerial),1=1,net_equipment_id like  concat('%',:netEquSerial,'%'))" +
            " and IF(ISNULL(:model),1=1,model like  concat('%',:model,'%'))" +
            " UNION  ALL " +
            " SELECT id,name,serial,communication_type,'' region,model,port,sequence,statute_code,'' relay_route " +
            " FROM comm_panel " +
            " WHERE 1=1 and IF(ISNULL(:id),1=1,id like  concat('%',:id,'%')) " +
            " and IF(ISNULL(:name),1=1,name like  concat('%',:name,'%'))  " +
            " and IF(ISNULL(:serial),1=1,serial like  concat('%',:serial,'%'))" +
            " and IF(ISNULL(:netEquSerial),1=1,net_equipment_id like  concat('%',:netEquSerial,'%'))" +
            " and IF(ISNULL(:model),1=1,model like  concat('%',:model,'%'))) ss",
            nativeQuery = true)
    Page<Object> findByCriOfPageForNodeEqu(@Param("id") String id, @Param("name") String name,
                                           @Param("serial") String serial, @Param("model") String model,
                                           @Param("netEquSerial") String netEquSerial, @Param("pageable") Pageable pageable);
//    Page<CommNetEquipmentVO> findByCriOfPage(@Param("commNetEquipment") CommNetEquipment commNetEquipment, @Param("pageable") Pageable pageable);


    @Query(value = "SELECT id,serial,service,type,model,timeout,elec_serial,name,mac,version FROM comm_net_equipment WHERE type!='站AI控制器' AND serial=:serial  "
            , countQuery = "SELECT count(id)FROM comm_net_equipment WHERE type!='站AI控制器' AND serial=:serial  ", nativeQuery = true)
    CommNetEquipment getOneOfFilter(@Param("serial") String serial);

    CommNetEquipment findByModelAndSerial(@Param("model") String model, @Param("serial") String serial);


    CommNetEquipment findBySerial(String serial);

    @Query("SELECT c FROM CommNetEquipment c WHERE c.serial IN (:serial)")
    List<CommNetEquipment> findAllBySerial(@Param("serial") List<String> serial);

}
